package com.example.edupublisher.mapper;

import com.example.edupublisher.bean.TrafficVisitorStatsPerHour;
import com.example.edupublisher.bean.TrafficVisitorTypeStats;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * ClassName: TrafficVisitorStatsMapper
 * Package: com.atguigu.edu.publisher.mapper
 * Description:
 *
 * @Author Mr.2
 * @Create 2023/9/11 11:47
 * @Version 1.0
 */
@Mapper
public interface TrafficVisitorStatsMapper {
    // 1. 分时流量数据查询
    @Select("SELECT\n" +
            "  HOUR(stt) hr,\n" +
            "  sum(uv_ct) uv_ct,\n" +
            "  sum(pv_ct) pv_ct,\n" +
            "  sum(if(is_new='1',uv_ct,0)) new_uv_ct,\n" +
            "  cast(sum(pv_ct)/sum(sv_ct) as decimal(10,0)) per_pv_session\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY hr;")
    List<TrafficVisitorStatsPerHour> selectVisitorStatsPerHr(@Param(value = "date") Integer date);

    // 2. 新老访客流量统计
    @Select("SELECT\n" +
            "  is_new,\n" +
            "  SUM(uv_ct) uv_ct,\n" +
            "  SUM(pv_ct) pv_ct,\n" +
            "  SUM(sv_ct) sv_ct,\n" +
            "  SUM(IF(pv_ct=1,pv_ct,0)) uj_ct,\n" +
            "  SUM(dur_sum) dur_sum\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY is_new;")
    List<TrafficVisitorTypeStats> selectVisitorTyoeStats(@Param(value = "date") Integer date);

}
